Allan B. Wilson; abwilson@awasu.com
Version 1.1 (second release); 19-Jun-04
The major change in this release is accommodating the new archiving version of Awasu, which supplies different information to channel hooks. Because of this fundamental change, the hook’s MySQL database structure is not the same as before. Therefore any data saved with the previous version of the hook are no longer usable, and the hook’s database table must be recreated as described herein; this will automatically deleted any news items previously saved.
This Channel Hook is invoked by Awasu when it detects the arrival of a new item in the associated channel feed; the hook saves the new item in a MySQL database. This means you can easily keep a history of selected news items in a powerful, freely available relational database that supports FULLTEXT searching.
There is also a companion command-line utility, usawa.exe, to search and view the database in HTML (in a browser). It provides a quick and easy interface to the stored news-item data, which otherwise would require manual SQL commands to view. Of course, once you have installed MySQL, its full power is available, so using the utility is optional—if you prefer using SQL, go for it!
There is no charge for personal or non-profit use. For commercial use or any required enhancements, please e-mail the author.
The MySQL database package must be installed before using this channel hook or the accompanying utility. If you have already installed MySQL, you can skip this section; however, you will still need to create a MySQL user account and password as well as the hook database and table; these steps are detailed in the next section.
To install MySQL, go to the download page at http://www.mysql.com/downloads and retrieve the Windows installation package for MySQL version 4.0 or later (when these notes were written, the starting links were listed under the heading “MySQL database server & standard clients”). Read the appropriate documentation to get MySQL installed and running.
For reference (not necessarily because it’s appropriate for your configuration or otherwise better than what you come up with), this is what my my.ini file looks like:
[mysqld] basedir=C:/mysql datadir=C:/mysql/data # Data file(s) must be able to # hold your data and indexes. # Make sure you have enough # free disk space. innodb_data_file_path = ibdata1:10M:autoextend # Set buffer pool size to # 50 - 80 % of your computer's # memory set-variable = innodb_buffer_pool_size=200M set-variable = innodb_additional_mem_pool_size=10M # Set the log file size to about # 25 % of the buffer pool size set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M # Set ..flush_log_at_trx_commit # to 0 if you can afford losing # some last transactions innodb_flush_log_at_trx_commit=1 skip-bdb
I have chosen to run mysqld-max-nt.exe as a Windows XP service. This is the full-featured version of the database for Windows; by running it as a service, MySQL is automatically available as soon as the system boots.
Once MySQL is successfully installed and running, you need to prepare it so the hook can use it. The hook has been created to use a default database (with one specific table) and run with a default user account/password; all of these except the details of the specific table can be changed if you wish. Of course you can simply use the “built-in” values and therefore you should use the exact commands below.
First, you need to create a database. Running as a MySQL administrator using the mysql database, execute the command:
CREATE DATABASE AwasuNewsItemDB;
Next, create a user account, with full privileges to the new database (account name awasu, password awasu), like this:
GRANT ALL PRIVILEGES ON AwasuNewsItemDB.* TO awasu@localhost IDENTIFIED BY ‘awasu’;
GRANT ALL PRIVILEGES ON awasuNewsItemDB.* TO awasu IDENTIFIED BY ‘awasu’;
Finally, create the single database table the hook uses by running createNewsItemTable.exe. This will execute the command:
CREATE TABLE AwasuNewsItemDB.NewsItems ( ItemId INTEGER NOT NULL, PRIMARY KEY (ItemId), TimeCreated DATETIME NOT NULL, INDEX timecreatedindex (TimeCreated), ItemTitle TEXT NULL, ItemUrl TEXT NULL, ItemMetadata TEXT NULL, PlainTextDescription MEDIUMTEXT NULL, MarkedUpDescription MEDIUMTEXT NULL, FULLTEXT (ItemTitle, ItemUrl, ItemMetadata, PlainTextDescription) ) TYPE=MyISAM CHARACTER SET utf8""")
Note that if you ever want to get rid of all previous news-item information in the database, you can execute the following command:
DROP TABLE IF EXISTS AwasuNewsItemDB.NewsItems;
If the table exists, the command above—without warning—destroys it and all its contents. This will wipe out any news-item data previously stored!
This is a standard Awasu channel hook. Install it by viewing Properties for an existing channel (by right-clicking on a channel in the Control Center and selecting Properties) or, in newer version of Awasu, you can set an advanced option to display the hook dialog when a channel is created. If you are viewing the properties of an existing channel, once the channel properties window is displayed, click on the “Hooks” icon. This will open a new window that lets you specify the hook. If instead you have set the Awasu option to display the Channel Hook dialog when creating a new channel, wait for it in the sequence of Awasu dialogs. Once the Channel Hook dialog box is open, click on the create-hook icon, which is the one to the left of the “X” icon (which “disconnects” a channel hook); then navigate to and select the channel hook.
Once selected, you’ll need to supply values to identify the database host system, MySQL user account and password, database to use for logging the associated RSS feed, and an optional logfile name. If the MySQL server is running on the system where Awasu is running, use “localhost” (or “127.0.0.1”) as the server name. Of course the user account, password, and database name should match the information you specified when you prepared MySQL for the hook, as described in the section immediately above.
You can run this channel hook on multiple channels to save multiple channel feed items simultaneously. You may store all the items in a single database or use a dedicated database for one or more channels.
Note that you must install MySQL before associating this hook with any Awasu channel. See the sections above for information about MySQL installation and configuration, and preparing it for use.
If you have problems getting the hook to run, turn on Awasu’s hook logging in the hook properties dialog (reached from its associated channel’s properties dialog, as described under Basic Operation). Then you can see any error output from the hook when Awasu invokes it. You can also enable the hook’s own logging facility by entering a logfile name in the hook creation dialog; examine the logfile to follow the hook’s execution progress.
Awasu invokes the hook each time it detects a new item in the associated channel. Once invoked, the hook saves a copy of the new item in the MySQL database and exits.
This plug-in was written in Python and the resulting code “frozen” to create the standalone executable file.
usawa.exe: A command-line utility to search the MySQL database for items and display matches in the default browserAs mentioned earlier, once you have installed MySQL on your system you have its full power available. You can use SQL to query the database, generate reports, show statistics, etc. However, if all you want to do is search the database for news items meeting certain conditions, you should give usawa.exe a try.
To use usawa.exe, open a command window (for example, by selecting Start/Run…, entering “cmd” in the Open box, and clicking OK). Once the command window is open, to see the program’s usage information and options enter “usawa.exe -h” (without the quotation marks, but—not shown—specifying the directory in which the program resides, if necessary) and press Enter.
A search can be as simple as “usawa.exe citation” to search for news items containing the text “citation”. You can search for multiple items simultaneously: To search for news items about Python or Perl, enter “usawa.exe python perl”.
If any news items match your search criteria, usawa.exe will display them in your default browser.
Please refer to the MySQL FULLTEXT documentation for complete searching details. However, in a nutshell, include words you want to find in the stored RSS feeds; feeds containing any of the words will match. Including any of the special characters below turns your search into a BOOLEAN search; again, refer to the documentation.
+ A leading plus sign indicates that this word must be present in a feed. - A leading minus sign indicates that this word must not be present in a feed. < > The < operator decreases a word's contribution to the match; the > operator increases it. ( ) Parentheses are used to group words into subexpressions. ~ A leading tilde acts as a negation operator, causing the word's contribution to the feed relevance to be negative. * An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word, not prepended. " A phrase enclosed in double quotes matches only feeds that contain this literal phrase.
Here are some examples:
apple banana
find feeds that contain at least one of these words.
+apple +juice
... both words.
+apple macintosh
... word “apple”, but rank it higher if it also contains “macintosh”.
+apple -macintosh
... word “apple” but not “macintosh”.
“some words”
... “some words of wisdom”, but not “some noise words”.